-- 创建触发器函数和触发器

-- 更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要自动更新updated_at字段的表创建触发器
CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_scripts_updated_at 
    BEFORE UPDATE ON scripts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_connections_updated_at 
    BEFORE UPDATE ON connections
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 文件夹路径自动更新触发器函数
CREATE OR REPLACE FUNCTION update_folder_path()
RETURNS TRIGGER AS $$
DECLARE
    parent_path VARCHAR(1000);
BEGIN
    -- 如果有父文件夹，构建完整路径
    IF NEW.parent_id IS NOT NULL THEN
        SELECT path INTO parent_path FROM folders WHERE id = NEW.parent_id;
        NEW.path = parent_path || '/' || NEW.name;
    ELSE
        -- 根文件夹
        NEW.path = NEW.name;
    END IF;
    
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为文件夹表创建路径更新触发器
CREATE TRIGGER update_folder_path_trigger
    BEFORE INSERT OR UPDATE ON folders
    FOR EACH ROW EXECUTE FUNCTION update_folder_path();

-- 版本号自动生成触发器函数
CREATE OR REPLACE FUNCTION generate_version_number()
RETURNS TRIGGER AS $$
DECLARE
    max_version INTEGER;
    new_version VARCHAR(50);
BEGIN
    -- 如果没有指定版本号，自动生成
    IF NEW.version IS NULL OR NEW.version = '' THEN
        -- 获取该脚本的最大版本号
        SELECT COALESCE(MAX(CAST(SUBSTRING(version FROM '^v(\d+)') AS INTEGER)), 0) 
        INTO max_version 
        FROM versions 
        WHERE script_id = NEW.script_id AND version ~ '^v\d+$';
        
        -- 生成新版本号
        new_version = 'v' || (max_version + 1);
        NEW.version = new_version;
    END IF;
    
    -- 生成内容哈希
    NEW.content_hash = encode(digest(NEW.content, 'sha256'), 'hex');
    
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为版本表创建版本号生成触发器
CREATE TRIGGER generate_version_number_trigger
    BEFORE INSERT ON versions
    FOR EACH ROW EXECUTE FUNCTION generate_version_number();

-- 更新脚本当前版本触发器函数
CREATE OR REPLACE FUNCTION update_script_current_version()
RETURNS TRIGGER AS $$
BEGIN
    -- 更新脚本表的当前版本
    UPDATE scripts 
    SET current_version = NEW.version, updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.script_id;
    
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为版本表创建脚本当前版本更新触发器
CREATE TRIGGER update_script_current_version_trigger
    AFTER INSERT ON versions
    FOR EACH ROW EXECUTE FUNCTION update_script_current_version();